package com.huidao.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.huidao.entity.Goods;
import com.huidao.entity.Shoppingcart;
import com.huidao.entity.User;
import com.huidao.util.JDBCUtil;

public class ShopDaoImpl implements ShopDao {
	Connection conn;
	PreparedStatement ps;
	ResultSet rs;

	@Override
	public boolean addShop(Shoppingcart shop) {
		// TODO Auto-generated method stub
		String sql="INSERT INTO SHOPPINGCART(GOODS_ID,GOODS_NUM,USER_ID)"+"VALUE(?,?,?)";
		try {
			conn=JDBCUtil.getConn();
			ps=conn.prepareStatement(sql);
			ps.setLong(1, shop.getGoods().getGoodsId());
			ps.setInt(2, shop.getGoodsNum());
			ps.setLong(3, shop.getUser().getUserId());
			int i=ps.executeUpdate();
			if(i>0){
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.free(conn, ps, rs);
		}
		return false;
	}

	@Override
	public boolean updateShop(Shoppingcart shop) {
		// TODO Auto-generated method stub
		String sql = "UPDATE SHOPPINGCART SET GOODS_NUM=GOODS_NUM+? WHERE GOODS_ID=? AND USER_ID=?";
		try {
			conn=JDBCUtil.getConn();
			ps=conn.prepareStatement(sql);
			ps.setInt(1, shop.getGoodsNum());
			ps.setLong(2, shop.getGoods().getGoodsId());
			ps.setLong(3, shop.getUser().getUserId());
			int i=ps.executeUpdate();
			if(i>0){
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.free(conn, ps, rs);
		}
		return false;
	}

	@Override
	public List<Shoppingcart> getList(int startnum, int pageSize, long userId) {
		List<Shoppingcart> list = new ArrayList<Shoppingcart>();
		String sql = "SELECT * FROM SHOPPINGCART a " +
				" LEFT JOIN GOODS b ON a.GOODS_ID=b.GOODS_ID " +
				" LEFT JOIN USER c ON b.USER_ID=c.USER_ID " +
				" WHERE a.USER_ID=? LIMIT ?,?";
		try {
			conn =JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setLong(1, userId);
			ps.setInt(2, startnum);
			ps.setInt(3, pageSize);
			rs = ps.executeQuery();
			while(rs.next()){
				Shoppingcart s = new Shoppingcart();
				s.setShoppingcartId(rs.getLong("SHOPPINGCART_ID"));
				s.setGoodsNum(rs.getInt("GOODS_NUM"));
				Goods goods = new Goods();
				goods.setGoodsId(rs.getLong("GOODS_ID"));
				goods.setGoodsPrice(rs.getDouble("GOODS_PRICE"));
				goods.setGoodsAccount(rs.getInt("GOODS_ACCOUNT"));
				goods.setGoodsName(rs.getString("GOODS_NAME"));
				User user = new User();
				user.setUserNickname(rs.getString("USER_NICKNAME"));
				user.setUserPhone(rs.getLong("USER_PHONE"));
				user.setUserShopname(rs.getString("USER_SHOPNAME"));
				goods.setUser(user);
				s.setGoods(goods);
				list.add(s);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.free(conn, ps, rs);
		}
		return list;
	}

	@Override
	public int getTotalCount(long userId) {
		// TODO Auto-generated method stub
		String sql = "SELECT COUNT(*) FROM SHOPPINGCART WHERE USER_ID=?";
		try {
			conn =JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setLong(1, userId);
			rs = ps.executeQuery();
			if(rs.next()){
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.free(conn, ps, rs);
		}
		return 0;
	}

	@Override
	public boolean delShop(long sId) {
		// TODO Auto-generated method stub
		String sql = "DELETE FROM SHOPPINGCART WHERE SHOPPINGCART_ID=?";
		try {
		    conn = JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setLong(1, sId);
			int i = ps.executeUpdate();
			if(i>0){
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.free(conn, ps, rs);
		}
		return false;
	}

	@Override
	public boolean delShop(Connection conn, long sId) {
		// TODO Auto-generated method stub
		String sql = "DELETE FROM SHOPPINGCART WHERE SHOPPINGCART_ID=?";
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setLong(1, sId);
			int i = ps.executeUpdate();
			if(i>0){
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
		   JDBCUtil.free(conn, ps, rs);
		}
		return false;
	}

	@Override
	public boolean clearShop(long userId) {
		// TODO Auto-generated method stub
		String sql = "DELETE FROM SHOPPINGCART WHERE USER_ID=?";
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setLong(1, userId);
			int i = ps.executeUpdate();
			if(i>0){
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.free(conn, ps, rs);
		}
		return false;
	}

	@Override
	public List<Shoppingcart> getAllList(long userId) {
		// TODO Auto-generated method stub
		List<Shoppingcart> list = new ArrayList<Shoppingcart>();
		String sql = "SELECT * FROM SHOPPINGCART a " +
				" WHERE a.USER_ID=?";
		try {
			conn =JDBCUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setLong(1, userId);
			rs = ps.executeQuery();
			while(rs.next()){
				Shoppingcart s = new Shoppingcart();
				s.setShoppingcartId(rs.getLong("SHOPPINGCART_ID"));
				s.setGoodsNum(rs.getInt("GOODS_NUM"));
				Goods goods = new Goods();
				goods.setGoodsId(rs.getLong("GOODS_ID"));
				s.setGoods(goods);
				list.add(s);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.free(conn, ps, rs);
		}
		return list;
	}

}
